package me.zhengjie.pay.service.impl;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import me.zhengjie.business.domain.Jzdj;
import me.zhengjie.business.domain.KB04;
import me.zhengjie.business.domain.sup.Gsgl;
import me.zhengjie.business.domain.vo.*;
import me.zhengjie.business.repository.SnryRepository;
import me.zhengjie.pay.domain.PaidQueryCriteria;
import me.zhengjie.pay.domain.QueryRefundVo;
import me.zhengjie.pay.domain.SettleAccount;
import me.zhengjie.pay.repository.SettleAccountRepository;
import me.zhengjie.pay.service.UnPaidService;
import me.zhengjie.utils.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

/**
 * @description 待缴费查询
 * @author xiahongbo
 * @date 2022-07-21
 **/
@Slf4j
@Service
@RequiredArgsConstructor
public class UnPaidServiceImpl implements UnPaidService {

    @PersistenceContext
    private EntityManager em;

    private final SettleAccountRepository settleAccountRepository;

    @Override
    public JSONObject queryUnPaidInfo(JSONObject obj) {
        String adlXmSql = "select  " +
                "R0.BIS_JZDJ_ID, " +
                "R0.BIS_JZDJ_ZHSQ_ID, " +
                "R1.BIS_FYMX_ID, " +
                "R0.BIS_JZDJ_FPH, " +
                "R1.BIS_FYMX_MC, " +
                "R1.BIS_FYMX_GG, " +
                "R1.BIS_FYMX_SL, " +
                "R1.BIS_FYMX_ZJ, " +
                "R1.BIS_FYMX_ZXKS, " +
                "R1.BIS_FYMX_GRBH, " +
                "R2.PAY_MSG_YBC, " +
                "R1.BIS_FYMX_DJ " +
                " from BIS_JZDJ R0, BIS_FYMX R1, PAY_MSG R2 " +
                "WHERE R0.BIS_JZDJ_ID=R1.BIS_FYMX_JZDJ_ID " +
                "AND R0.BIS_JZDJ_SFZ=R2.PAY_MSG_SFZ " +
                "AND R1.BIS_FYMX_ID=R2.PAY_MSG_FYID " +
                "AND R0.BIS_JZDJ_CYZT='0' " +
                "AND R0.BIS_JZDJ_LSH=:lsh ";
        Query adlXmQuery = em.createNativeQuery(adlXmSql);
        adlXmQuery.setParameter("lsh", obj.get("VisitID"));
        List adlXmList = adlXmQuery.getResultList();
        JSONArray DetailInfoArray = new JSONArray();
        for(Object adlXmObj : adlXmList) {
            Object[] adlXmArr = (Object[]) adlXmObj;
            //   System.out.println(adlXmArr[0]+"---"+adlXmArr[1]);
            BigDecimal jzid = (BigDecimal)adlXmArr[0];
            BigDecimal zhid = (BigDecimal)adlXmArr[1];
            BigDecimal fyid = (BigDecimal)adlXmArr[2];
            String fph = (String)adlXmArr[3];
            String xmmc = (String)adlXmArr[4];
            String gg = (String)adlXmArr[5];
            BigDecimal sl = (BigDecimal)adlXmArr[6];
            BigDecimal zj = (BigDecimal)adlXmArr[7];
            String zxks = (String)adlXmArr[8];
            String grbh = (String)adlXmArr[9];
            String ybc = (String)adlXmArr[10];
            BigDecimal dj = (BigDecimal)adlXmArr[11];

            JSONObject DetailInfoObject = new JSONObject();
            // 病人ID
            DetailInfoObject.put("PatientID", zhid.toPlainString());
            // 门诊号
            DetailInfoObject.put("OutpatientNum", obj.get("VisitID"));
            // 明细ID
            DetailInfoObject.put("DetailID", fyid.toPlainString());
            // 发票号
            DetailInfoObject.put("InvoiceID", fph);
            // 费用信息串

            JSONObject DetailJson = new JSONObject();
            // 医院编码
            DetailJson.put("HospitalCode", obj.get("HospitalCode"));
            // 病人ID
            DetailJson.put("PatientID", zhid.toPlainString());
            // 就诊ID
            DetailJson.put("VisitID", obj.get("VisitID"));
            // 类型ID
            DetailJson.put("TypeID", "2");
            // 主记录ID
            DetailJson.put("RecordID", "");
            // 项目名称
            DetailJson.put("ItemName", xmmc);
            // 项目规格
            DetailJson.put("ItemSize", gg);
            // 项目数量
            DetailJson.put("ItemMount", sl.toPlainString()+"*"+dj.toPlainString());
            // 项目总费用
            DetailJson.put("ItemCost", zj.toPlainString());
            // 明细ID
            DetailJson.put("DetailID", fyid.toPlainString());
            // 执行科室
            DetailJson.put("ExecDept", zxks);
            DetailInfoObject.put("DetailJson", DetailJson);
            // 医保接口串
            JSONObject MedicareJson = new JSONObject();
            // 医院编码
            MedicareJson.put("HospitalCode", obj.get("HospitalCode"));
            // 医保授权码
            MedicareJson.put("AccreditKey", obj.get("AccreditKey"));
            // 医保交易码
            MedicareJson.put("BusinessCode", "2310");
            // 个人编号
            MedicareJson.put("PersonNum", grbh);
            // 医保交易串
            MedicareJson.put("MediInParam", ybc);
            DetailInfoObject.put("MedicareJson", MedicareJson);

            DetailInfoArray.add(DetailInfoObject);
        }



        JSONObject resultObject = new JSONObject();

        // 交易代码
        resultObject.put("TranCode", obj.get("TranCode"));
        // 处理结果
        resultObject.put("ResultCode", "0");
        // 错误信息
        resultObject.put("ErrorMsg", "");
        // 院区编码
        resultObject.put("HosAreaCode", obj.get("HospitalCode"));
        // 费用信息
        resultObject.put("DetailInfo", DetailInfoArray);
        // 随机字符串
        resultObject.put("NonceStr", IdUtil.randomUUID());
        // 签名
        resultObject.put("Sign", "");

        return resultObject;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public JSONObject settleAccounts(JSONObject obj) {

        SettleAccount settleAccount = new SettleAccount();
        // 交易流水号
        settleAccount.setId(IdUtil.simpleUUID());
        // 交易流水号
        settleAccount.setTranFlowNo(obj.getString("TranFlowNo"));
        // 病人ID
        settleAccount.setPatientId(obj.getLong("PatientID"));
        // 就诊ID
        settleAccount.setVisitId(obj.getString("VisitID"));
        // 发票号
        settleAccount.setInvoiceId(obj.getString("InvoiceID"));
        // 挂号ID
        settleAccount.setRegistId(obj.getString("RegistID"));
        // 总费用
        settleAccount.setFee(obj.getBigDecimal("Fee"));
        // 自费金额
        settleAccount.setCashAmt(obj.getBigDecimal("CashAmt"));
        // 支付方式
        settleAccount.setFirstPayment(obj.getString("FirstPayment"));
        // 支付金额
        settleAccount.setFirstAmt(obj.getBigDecimal("FirstAmt"));
        // 交易时间
        settleAccount.setTradeTime(obj.getString("TradeTime"));
        // 退费状态（本地控制）
        settleAccount.setRefundStas("0");

        settleAccountRepository.save(settleAccount);

        String adlXmSql = "SELECT " +
                "R1.BIS_JZDJ_ID, " +
                "R2.BIS_ORDER_ID, " +
                "R2.BIS_ORDER_HLY_NAME " +
                "FROM BIS_JZDJ R1, BIS_ORDER R2 " +
                "WHERE R1.BIS_JZDJ_ORDER_ID = R2.BIS_ORDER_ID " +
                "AND R1.BIS_JZDJ_FPH = :fph ";
        Query adlXmQuery = em.createNativeQuery(adlXmSql);
        adlXmQuery.setParameter("fph", obj.get("InvoiceID"));
        List adlXmList = adlXmQuery.getResultList();
        Object[] adlXmArr = (Object[]) adlXmList.get(0);
        BigDecimal jzid = (BigDecimal)adlXmArr[0];
        String orderid = (String) adlXmArr[1];
        String hryxm = (String) adlXmArr[2];

        // 修改订单的状态
        String updateOrderSql = "UPDATE BIS_ORDER SET BIS_ORDER_ZT='2' WHERE BIS_ORDER_ID = :orderID";
        Query queryOrder = em.createNativeQuery(updateOrderSql);
        queryOrder.setParameter("orderID", orderid);
        queryOrder.executeUpdate();

        // 修改就诊登记的状态
        String updateJzxxSql = "UPDATE BIS_JZDJ SET BIS_JZDJ_CYZT='1' WHERE BIS_JZDJ_ID = :jzid";
        Query queryJzxx = em.createNativeQuery(updateJzxxSql);
        queryJzxx.setParameter("jzid", jzid);
        queryJzxx.executeUpdate();

        JSONObject resultObject = new JSONObject();

        // 交易代码
        resultObject.put("TranCode", "1819006");
        // 处理结果
        resultObject.put("ResultCode", "0");
        // 错误信息
        resultObject.put("ErrorMsg", "");
        // 交易时间
        resultObject.put("TranDateTime", DateUtil.format(DateUtil.date(), DatePattern.PURE_DATETIME_PATTERN));
        // 总费用
        resultObject.put("Fee", obj.get("Fee"));
        // 支付ID
        resultObject.put("PaymentID", orderid);
        // 发票ID
        resultObject.put("InvoiceID", obj.get("InvoiceID"));
        // 收费ID
        resultObject.put("ChargeID", settleAccount.getId());
        // 病人ID
        resultObject.put("PatientID", obj.getString("PatientID"));
        // 发药窗口
        resultObject.put("DispensaryWindow", hryxm);
        // 随机字符串
        resultObject.put("NonceStr", IdUtil.randomUUID());
        // 签名
        resultObject.put("Sign", "");

        return resultObject;
    }

    @Override
    public Map<String, Object> refundList(String blurry, Pageable pageable) {
        Page<QueryRefundVo> page =  settleAccountRepository.findCityAndHotelAllSelf(blurry, pageable);
        return PageUtil.toPage(page);
    }

    @Override
    public Map<String, Object> queryKb03(PaidQueryCriteria criteria, Pageable pageable) {
        Page<KB03> page =  settleAccountRepository.queryKb03(criteria.getAkb020(),criteria.getAae002(),criteria.getInsuNo(), pageable);
        return PageUtil.toPage(page);
    }

    @Override
    public List<KB03> queryKb03(String akb020, String aae002, String insu_no, String cjt100, String aab034) {
        List<KB03> list = new ArrayList<>();
        String aab034Sql = "长春市医保".equals(aab034) ? " and aab034 = '长春市医保' " : " and aab034 != '长春市医保' ";
        String sql = "select * from " +
                "(select aab034, aae016, to_char(to_number(akb062)), to_char(ckb152), to_char(akb068), to_char(akb064), to_char(ckb161), to_char(ckb169) " +
                "  from kb03 t " +
                " where t.akb020 = :akb020 " +
                "   and t.aae002 = :aae002 " +
                "   and insu_no = :insu_no " +
                "   and cjt100 = :cjt100 " + aab034Sql +
                "union all " +
                "select '', '', to_char(sum(akb062)), to_char(sum(ckb152)), to_char(sum(akb068)), to_char(sum(akb064)), to_char(sum(ckb161)), to_char(sum(ckb169)) " +
                "  from kb03 t " +
                " where t.akb020 = :akb020 " +
                "   and t.aae002 = :aae002 " +
                "   and insu_no = :insu_no " +
                "   and cjt100 = :cjt100 " + aab034Sql +
                "   ) " +
                "order by aab034 ";
        Query adlXmQuery = em.createNativeQuery(sql);
        adlXmQuery.setParameter("akb020", akb020);
        adlXmQuery.setParameter("aae002", aae002);
        adlXmQuery.setParameter("insu_no", insu_no);
        adlXmQuery.setParameter("cjt100", cjt100);
        List adlXmList = adlXmQuery.getResultList();
        for(Object adlXmObj : adlXmList) {
            KB03 kb03 = new KB03();
            Object[] adlXmArr = (Object[]) adlXmObj;
            String aab0341 = adlXmArr[0] == null ? "" : (String)adlXmArr[0];
            String aae016 = adlXmArr[1] == null ? "" : (String)adlXmArr[1];
            String akb062 = adlXmArr[2] == null ? "" : (String)adlXmArr[2];
            String ckb152 = adlXmArr[3] == null ? "" : (String)adlXmArr[3];
            String akb068 = adlXmArr[4] == null ? "" : (String)adlXmArr[4];
            String akb064 = adlXmArr[5] == null ? "" : (String)adlXmArr[5];
            String ckb161 = adlXmArr[6] == null ? "" : (String)adlXmArr[6];
            String ckb169 = adlXmArr[7] == null ? "" : (String)adlXmArr[7];
            kb03.setAab034(aab0341);
            kb03.setAae016(aae016);
            kb03.setAkb062(akb062);
            kb03.setCkb152(ckb152);
            kb03.setAkb068(akb068);
            kb03.setAkb064(akb064);
            kb03.setCkb161(ckb161);
            kb03.setCkb169(ckb169);
            list.add(kb03);
        }
        return list;
    }

    @Override
    public List<KB04> queryKb04(String akb020, String aae001, String insu_no, String cjt100, String aae140) {
        List<KB04> list = new ArrayList<>();
        String sql = "select t.aab034_name, " +
                "       扣款原因, " +
                "       to_char(医疗费总额), " +
                "       to_char(个人现金支付金额), " +
                "       to_char(统筹支付金额), " +
                "       to_char(预留风险基金总额), " +
                "       to_char(年底考核扣款金额), " +
                "       to_char(应支付金额总计), " +
                "       t.中文, " +
                "       t.aae140 " +
                "  from KB04_NEW t " +
                " where t.aae001 = :aae001  " +
                "   and t.cjt100 = :cjt100  " +
                "   and t.aae140 = :aae140  " +
                "   and t.服务机构代码 = :akb020  " +
                "   and t.aab034_bf = :insu_no " +
                " order by t.服务机构代码, t.cjt100, T.AAE140 DESC, t.aab034_bf, t.aab034 ";
        Query adlXmQuery = em.createNativeQuery(sql);
        adlXmQuery.setParameter("akb020", akb020);
        adlXmQuery.setParameter("cjt100", cjt100);
        adlXmQuery.setParameter("aae140", aae140);
        adlXmQuery.setParameter("aae001", aae001);
        adlXmQuery.setParameter("insu_no", insu_no);
        List adlXmList = adlXmQuery.getResultList();
        for(Object adlXmObj : adlXmList) {
            KB04 kb04 = new KB04();
            Object[] adlXmArr = (Object[]) adlXmObj;
            String aab034 = adlXmArr[0] == null ? "" : (String)adlXmArr[0];
            String akb083 = adlXmArr[1] == null ? "" : (String)adlXmArr[1];
            String akb065 = adlXmArr[2] == null ? "" : (String)adlXmArr[2];
            String akb067 = adlXmArr[3] == null ? "" : (String)adlXmArr[3];
            String akb068 = adlXmArr[4] == null ? "" : (String)adlXmArr[4];
            String akb090 = adlXmArr[5] == null ? "" : (String)adlXmArr[5];
            String akb071 = adlXmArr[6] == null ? "" : (String)adlXmArr[6];
            String akb094 = adlXmArr[7] == null ? "" : (String)adlXmArr[7];
            String totle = adlXmArr[8] == null ? "" : (String)adlXmArr[8];
            String tempAae140 = adlXmArr[9] == null ? "" : (String)adlXmArr[9];
            kb04.setAab034(aab034);
            kb04.setAkb083(akb083.trim());
            kb04.setAkb065(new BigDecimal(akb065));
            kb04.setAkb067(new BigDecimal(akb067));
            kb04.setAkb068(new BigDecimal(akb068));
            kb04.setAkb090(new BigDecimal(akb090));
            kb04.setAkb071(new BigDecimal(akb071));
            kb04.setAkb094(new BigDecimal(akb094));
            kb04.setId(totle);
            kb04.setAae140(tempAae140);
            list.add(kb04);
        }
        return list;
    }

    @Override
    public Map<String, Object> queryKb22(PaidQueryCriteria criteria, Pageable pageable) {
        Page<KB22> page =  settleAccountRepository.queryKb22(criteria.getAkb020(), criteria.getAae002(), criteria.getCjt100(), criteria.getAaa022(), criteria.getInsuNo(), criteria.getAab034(), criteria.getRyxx(), pageable);
        return PageUtil.toPage(page);
    }

    /**
     * 查询外县参保人员在长发生的费用
     * @param akb020
     * @param aae002
     * @param insu_no
     * @param cjt100
     * @param aab034
     * @param startEnd
     * @return
     */
    @Override
    public  Object queryKb221(String akb020, String aae002, String insu_no, String cjt100, String aab034, int[] startEnd) {

        String akb020Sql = (akb020 == null || "".equals(akb020) || akb020 == "null") ? "" : "   r0.akb020='"+akb020+"' and ";
        String cjt100Sql = (cjt100 == null || "".equals(cjt100) || cjt100 == "null") ? "" : "   r0.cjt100='"+cjt100+"' and ";
        String aae002Sql = (aae002 == null || "".equals(aae002) || aae002 == "null") ? "" : "   r0.aae002='"+aae002+"' and ";
        String aab034Sql = (aab034 == null || "".equals(aab034) || aab034 == "null") ? "" : "   r0.aab034='"+aab034+"' and ";

        String sql = "select r0.id, " +
                "       r0.akb020, " +
                "       r2.akb021, " +
                "       r0.aae002, " +
                "       to_char(r0.aae015), " +
                "       r0.cjt100, " +
                "       r0.aab034, " +
                "       r0.aac001, " +
                "       r0.aac003, " +
                "       r0.aac002, " +
                "       to_char(r0.akc192, 'yyyy-mm-dd'), " +
                "       to_char(r0.akc194, 'yyyy-mm-dd'), " +
                "       to_char(r0.akc264), " +
                "       to_char(r0.akc260), " +
                "       to_char(r0.akc255), " +
                "       to_char(r0.akc261), " +
                "       to_char(r0.aaa005), " +
                "       to_char(r0.yl), " +
                "       to_char(r0.dqbo), " +
                "       to_char(r0.zkc024), " +
                "       to_char(r0.akc268), " +
                "       to_char(r0.kjsl), " +
                "       to_char(r0.hlkjje), " +
                "       to_char(r0.hckjje), " +
                "       to_char(r0.akc265), " +
                "       to_char(r0.ckc283), " +
                "       r0.adl_total, " +
                "       r0.aae013, " +
                "       r0.aaa022 from kb22 r0, " +
                "   ( " +
                "       select akb020, akb021, aae002,  " +
                "       case cjt100 when '职工' then '0' else '1' end cjt100, " +
                "       case aab034 when '双阳医保' then '220112' " +
                "           when '农安医保' then '220122' " +
                "           when '九台医保' then '220181' " +
                "           when '榆树医保' then '220182' " +
                "           when '德惠医保' then '220183' " +
                "            when '公主岭医保' then '220184' else '220101' end aab034 " +
                "       from kb03 where  aab034!='长春市医保'  " +
                "       and insu_no ='220101' " +
                "   ) r2 " +
                "   where  " +
                "   r0.aae002=r2.aae002 and  " +
                "   r0.akb020=r2.akb020 and  " +
                "   r0.cjt100=r2.cjt100 and  " +
                "   r0.aab034=r2.aab034 and " + akb020Sql + cjt100Sql + aae002Sql + aab034Sql +
                "   1=1 " +
                "   order by r0.aae002, r0.AKB020 "
                ;
        Query adlXmQuery = em.createNativeQuery(sql);
        adlXmQuery.setFirstResult(startEnd[0]);
        adlXmQuery.setMaxResults(startEnd[1] - startEnd[0]);

        List adlXmList = adlXmQuery.getResultList();
        List<KB221> list = new ArrayList<>();
        for(Object adlXmObj : adlXmList) {
            KB221 kb221 = new KB221();
            Object[] adlXmArr = (Object[]) adlXmObj;
            kb221.setId(adlXmArr[0] == null ? "" : (String)adlXmArr[0]);
            kb221.setAkb020(adlXmArr[1] == null ? "" : (String)adlXmArr[1]);
            kb221.setAkb021(adlXmArr[2] == null ? "" : (String)adlXmArr[2]);
            kb221.setAae002(adlXmArr[3] == null ? "" : (String)adlXmArr[3]);
            kb221.setAae015(adlXmArr[4] == null ? "" : (String)adlXmArr[4]);
            kb221.setCjt100(adlXmArr[5] == null ? "" : (String)adlXmArr[5]);
            kb221.setAab034(adlXmArr[6] == null ? "" : (String)adlXmArr[6]);
            kb221.setAac001(adlXmArr[7] == null ? "" : (String)adlXmArr[7]);
            kb221.setAac003(adlXmArr[8] == null ? "" : (String)adlXmArr[8]);
            kb221.setAac002(adlXmArr[9] == null ? "" : (String)adlXmArr[9]);
            kb221.setAkc192(adlXmArr[10] == null ? "" : (String)adlXmArr[10]);
            kb221.setAkc194(adlXmArr[11] == null ? "" : (String)adlXmArr[11]);
            kb221.setAkc264(adlXmArr[12] == null ? "" : (String)adlXmArr[12]);
            kb221.setAkc260(adlXmArr[13] == null ? "" : (String)adlXmArr[13]);
            kb221.setAkc255(adlXmArr[14] == null ? "" : (String)adlXmArr[14]);
            kb221.setAkc261(adlXmArr[15] == null ? "" : (String)adlXmArr[15]);
            kb221.setAaa005(adlXmArr[16] == null ? "" : (String)adlXmArr[16]);
            kb221.setYl(adlXmArr[17] == null ? "" : (String)adlXmArr[17]);
            kb221.setDqbo(adlXmArr[18] == null ? "" : (String)adlXmArr[18]);
            kb221.setZkc024(adlXmArr[19] == null ? "" : (String)adlXmArr[19]);
            kb221.setAkc268(adlXmArr[20] == null ? "" : (String)adlXmArr[20]);
            kb221.setKjsl(adlXmArr[21] == null ? "" : (String)adlXmArr[21]);
            kb221.setHlkjje(adlXmArr[22] == null ? "" : (String)adlXmArr[22]);
            kb221.setHckjje(adlXmArr[23] == null ? "" : (String)adlXmArr[23]);
            kb221.setAkc265(adlXmArr[24] == null ? "" : (String)adlXmArr[24]);
            kb221.setCkc283(adlXmArr[25] == null ? "" : (String)adlXmArr[25]);
            kb221.setAdlTotal(adlXmArr[26] == null ? "" : (String)adlXmArr[26]);
            kb221.setAae013(adlXmArr[27] == null ? "" : (String)adlXmArr[27]);
            kb221.setAaa022(adlXmArr[28] == null ? "" : (String)adlXmArr[28]);
            list.add(kb221);
        }

        String countSql = "select count(*) from kb22 r0, " +
                "   ( " +
                "       select akb020, akb021, aae002,  " +
                "       case cjt100 when '职工' then '0' else '1' end cjt100, " +
                "       case aab034 when '双阳医保' then '220112' " +
                "           when '农安医保' then '220122' " +
                "           when '九台医保' then '220181' " +
                "           when '榆树医保' then '220182' " +
                "           when '德惠医保' then '220183' " +
                "            when '公主岭医保' then '220184' else '220101' end aab034 " +
                "       from kb03 where  aab034!='长春市医保'  " +
                "       and insu_no ='220101' " +
                "   ) r2 " +
                "   where  " +
                "   r0.aae002=r2.aae002 and  " +
                "   r0.akb020=r2.akb020 and  " +
                "   r0.cjt100=r2.cjt100 and  " +
                "   r0.aab034=r2.aab034 and " + akb020Sql + cjt100Sql + aae002Sql + aab034Sql +
                "   1=1 "
                ;
        Query queryCount = em.createNativeQuery(countSql);

        Object totalElements = queryCount.getSingleResult();
        Map<String,Object> m = PageUtil.toPage(list, totalElements);
        return m;
    }

    @Override
    public void queryKb221Down(String akb020, String aae002, String insu_no, String cjt100, String aab034, HttpServletResponse response) throws IOException {

        String akb020Sql = (akb020 == null || "".equals(akb020) || akb020 == "null") ? "" : "   r0.akb020='"+akb020+"' and ";
        String cjt100Sql = (cjt100 == null || "".equals(cjt100) || cjt100 == "null") ? "" : "   r0.cjt100='"+cjt100+"' and ";
        String aae002Sql = (aae002 == null || "".equals(aae002) || aae002 == "null") ? "" : "   r0.aae002='"+aae002+"' and ";
        String aab034Sql = (aab034 == null || "".equals(aab034) || aab034 == "null") ? "" : "   r0.aab034='"+aab034+"' and ";

        String sql = "select r0.id, " +
                "       r0.akb020, " +
                "       r2.akb021, " +
                "       r0.aae002, " +
                "       to_char(r0.aae015), " +
                "       r0.cjt100, " +
                "       r0.aab034, " +
                "       r0.aac001, " +
                "       r0.aac003, " +
                "       r0.aac002, " +
                "       to_char(r0.akc192, 'yyyy-mm-dd'), " +
                "       to_char(r0.akc194, 'yyyy-mm-dd'), " +
                "       to_char(r0.akc264), " +
                "       to_char(r0.akc260), " +
                "       to_char(r0.akc255), " +
                "       to_char(r0.akc261), " +
                "       to_char(r0.aaa005), " +
                "       to_char(r0.yl), " +
                "       to_char(r0.dqbo), " +
                "       to_char(r0.zkc024), " +
                "       to_char(r0.akc268), " +
                "       to_char(r0.kjsl), " +
                "       to_char(r0.hlkjje), " +
                "       to_char(r0.hckjje), " +
                "       to_char(r0.akc265), " +
                "       to_char(r0.ckc283), " +
                "       r0.adl_total, " +
                "       r0.aae013, " +
                "       r0.aaa022 from kb22 r0, " +
                "   ( " +
                "       select akb020, akb021, aae002,  " +
                "       case cjt100 when '职工' then '0' else '1' end cjt100, " +
                "       case aab034 when '双阳医保' then '220112' " +
                "           when '农安医保' then '220122' " +
                "           when '九台医保' then '220181' " +
                "           when '榆树医保' then '220182' " +
                "           when '德惠医保' then '220183' " +
                "            when '公主岭医保' then '220184' else '220101' end aab034 " +
                "       from kb03 where  aab034!='长春市医保'  " +
                "       and insu_no ='220101' " +
                "   ) r2 " +
                "   where  " +
                "   r0.aae002=r2.aae002 and  " +
                "   r0.akb020=r2.akb020 and  " +
                "   r0.cjt100=r2.cjt100 and  " +
                "   r0.aab034=r2.aab034 and " + akb020Sql + cjt100Sql + aae002Sql + aab034Sql +
                "   1=1 " +
                "   order by r0.aae002, r0.AKB020 "
                ;
        Query adlXmQuery = em.createNativeQuery(sql);

        List adlXmList = adlXmQuery.getResultList();
        List<Map<String, Object>> list = new ArrayList<>();
        int cnt = 0;
        for(Object adlXmObj : adlXmList) {
            cnt++;
            Map<String,Object> map = new LinkedHashMap<>();
            Object[] adlXmArr = (Object[]) adlXmObj;
            map.put("序号", cnt);
            map.put("服务机构编号", adlXmArr[0] == null ? "" : (String)adlXmArr[0]);
            map.put("服务机构名称", adlXmArr[1] == null ? "" : (String)adlXmArr[1]);
            map.put("结算期", adlXmArr[2] == null ? "" : (String)adlXmArr[2]);
            map.put("照护类型", adlXmArr[28] == null ? "" : "1".equals((String)adlXmArr[28]) ? "机构" : "居家");
            map.put("参保类型", adlXmArr[5] == null ? "" : "0".equals((String)adlXmArr[5]) ? "职工" : "居民");
            map.put("姓名", adlXmArr[8] == null ? "" : (String)adlXmArr[8]);
            map.put("身份证", adlXmArr[9] == null ? "" : (String)adlXmArr[9]);
            map.put("统筹区", adlXmArr[6] == null ? "" : (String)adlXmArr[6]);
            map.put("入院日期", adlXmArr[10] == null ? "" : (String)adlXmArr[10]);
            map.put("出院日期", adlXmArr[11] == null ? "" : (String)adlXmArr[11]);
            map.put("费用总额", adlXmArr[12] == null ? "" : (String)adlXmArr[12]);
            map.put("统筹报销", adlXmArr[13] == null ? "" : (String)adlXmArr[13]);
            map.put("账户支付", adlXmArr[14] == null ? "" : (String)adlXmArr[14]);
            map.put("个人现金", adlXmArr[15] == null ? "" : (String)adlXmArr[15]);
            map.put("拨付金额", adlXmArr[16] == null ? "" : (String)adlXmArr[16]);
            map.put("预留", adlXmArr[17] == null ? "" : (String)adlXmArr[17]);
            map.put("当期拨付", adlXmArr[18] == null ? "" : (String)adlXmArr[18]);
            map.put("养老机构录入照护数量", adlXmArr[19] == null ? "" : (String)adlXmArr[19]);
            map.put("通过计算实际拨付照护数量", adlXmArr[20] == null ? "" : (String)adlXmArr[20]);
            map.put("护理扣减数量", adlXmArr[21] == null ? "" : (String)adlXmArr[21]);
            map.put("护理扣减金额", adlXmArr[22] == null ? "" : (String)adlXmArr[22]);
            map.put("耗材扣减金额", adlXmArr[23] == null ? "" : (String)adlXmArr[23]);
            map.put("本次理论照护数量", adlXmArr[24] == null ? "" : (String)adlXmArr[24]);
            map.put("扣减天数", adlXmArr[25] == null ? "" : (String)adlXmArr[25]);
            map.put("ADL总分", adlXmArr[26] == null ? "" : (String)adlXmArr[26]);
            map.put("异常备注", adlXmArr[27] == null ? "" : (String)adlXmArr[27]);
            list.add(map);
        }

        FileUtil.downloadExcel(list, response);
    }
}
